﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml.Linq;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace SQLDocumentationX
{
    public class DatabaseHelper
    {
        private XElement GetChildDependency(SqlSmoObject sso, Server svr)
        {
            DependencyWalker walker = new DependencyWalker(svr);
            DependencyTree tree = walker.DiscoverDependencies(new[] { sso }, DependencyType.Children);

            return new XElement("ChildDependencies"
                        , from d in walker.WalkDependencies(tree)
                          where d.Urn != sso.Urn
                          orderby d.Urn.Type descending
                          select new XElement("ChildDependency"
                              , new XElement("Type", d.Urn.Type)
                              , new XElement("Schema", d.Urn.XPathExpression.GetAttribute("Schema", d.Urn.Type))
                              , new XElement("Name", d.Urn.XPathExpression.GetAttribute("Name", d.Urn.Type))
                              ));

        }

        private XElement GetParentDependency(SqlSmoObject sso, Server svr)
        {
            DependencyWalker walker = new DependencyWalker(svr);
            DependencyTree tree = walker.DiscoverDependencies(new[] { sso }, DependencyType.Parents);

            return new XElement("ParentDependencies"
                        , from d in walker.WalkDependencies(tree)
                          where d.Urn != sso.Urn
                          orderby d.Urn.Type descending
                          select new XElement("ParentDependency"
                              , new XElement("Type", d.Urn.Type)
                              , new XElement("Schema", d.Urn.XPathExpression.GetAttribute("Schema", d.Urn.Type))
                              , new XElement("Name", d.Urn.XPathExpression.GetAttribute("Name", d.Urn.Type))
                              ));
        }
        /// <summary>
        /// 这是返回一个表或者视图的XML描述。
        /// </summary>
        /// <param name="tvb">表或者视图的名称</param>
        /// <param name="svr">服务器对象</param>
        /// <param name="version">脚本版本</param>
        /// <param name="type">表还是视图：这是为了在格式化的时候能区分</param>
        /// <returns></returns>
        public XDocument GetTableView(TableViewBase tvb, Server svr, string version,string type)
        {

            return new XDocument(
                new XDeclaration("1.0", "UTF-8", "yes"),
                new XProcessingInstruction("xml-stylesheet", "type='text/xsl' href='style/TableView.xslt'"),
                new XElement("TableView"
                    , new XAttribute("Type",type)//用这个属性记录当前是表还是一个视图
                    , new XAttribute("Name", tvb.Name)
                    , new XAttribute("Schema",tvb.Schema)
                    , new XElement("Properties"
                        , from Property p in tvb.Properties
                          select new XElement("Property"
                              , new XElement("Name", p.Name)
                              , new XElement("Value", p.Value)
                              ))
                    , new XElement("ExtendedProperties"
                        , from ExtendedProperty p in tvb.ExtendedProperties
                          select new XElement("ExtendedProperty"
                              , new XElement("Name", p.Name)
                              , new XElement("Value", p.Value)
                              ))
                    , new XElement("Columns"
                        , from Column c in tvb.Columns
                          select new XElement("Column"
                              , new XElement("Name", c.Name)
                              , new XElement("Type", c.DataType)
                              , new XElement("IsPrimaryKey", c.InPrimaryKey)
                              , new XElement("IsForeignKey", c.IsForeignKey)
                              , new XElement("Nullable", c.Nullable)
                              , new XElement("ExtendedProperties"
                                  , new XAttribute("Count", c.ExtendedProperties.Count)
                                  , from ExtendedProperty p in c.ExtendedProperties
                                    select new XElement("ExtendedProperty"
                                        , new XElement("Name", p.Name)
                                        , new XElement("Value", p.Value)
                                        ))
                                   ))
                    , new XElement("Indexes"
                        , from Index i in tvb.Indexes
                          select new XElement("Index"
                              , new XElement("Name", i.Name)
                              , new XElement("IsClustered", i.IsClustered)
                              ))

                    , new XElement("Triggers"
                        , from Trigger t in tvb.Triggers
                          select new XElement("Trigger"
                              , new XElement("Name", t.Name)
                              , new XElement("Insert", t.Insert)
                              , new XElement("Delete", t.Delete)
                              , new XElement("Update", t.Update)
                              , new XElement("InsteadOf", t.InsteadOf)

                              ))
                    , GetChildDependency(tvb, svr)
                    , GetParentDependency(tvb, svr)
                    , new XElement("Script", new XCData(GetScript(tvb, svr, version)))

                         ));

        }

        private string GetScript(SqlSmoObject sso, Server svr, string version)
        {

            try
            {
                ScriptingOptions options = new ScriptingOptions();
                options.ExtendedProperties = true;
                options.WithDependencies = true;
                options.IncludeIfNotExists = true;
                options.IncludeDatabaseContext = true;

                switch (version)
                {
                    case "SQL Server 2000":
                        options.TargetServerVersion = SqlServerVersion.Version80; break;
                    case "SQL Server 2005":
                        options.TargetServerVersion = SqlServerVersion.Version90; break;
                    case "SQL Server 2008":
                        options.TargetServerVersion = SqlServerVersion.Version100; break;
                    default:
                        break;
                }

                Scripter script = new Scripter(svr);
                script.Options = options;


                var q = script.Script(new[] { sso });
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("<font color='red'><b>--当前按照{0}版本生成脚本</b></font><br />", version);
                sb.AppendFormat("<font color='red'><b>--工具:{0},版本{1},{2},生成时间:{3}</b></font><br />", Utility.AssemblyTitle(), Utility.Version(), Utility.AssemblyCopyright(), DateTime.Now.ToString());
                sb.AppendLine("<br />");

                foreach (var item in q)
                {
                    sb.AppendLine(item);
                    sb.AppendLine("<br />");
                    sb.AppendLine("<font color='red'>GO</font>");
                    sb.AppendLine("<br />");
                    sb.AppendLine("<br />");

                }

                return sb.ToString();
            }
            catch (Exception ex)
            {
                return string.Format("<font color='red'>编写脚本时遇到如下错误:{0}</font>", ex.Message);
            }
        }

        /// <summary>
        /// 这个方法是取得函数或者存储过程的XML描述。
        /// </summary>
        /// <param name="ssob">函数或者存储过程对象</param>
        /// <param name="svr">服务器对象</param>
        /// <param name="version">脚本版本</param>
        /// <param name="type">是函数还是存储过程</param>
        /// <returns></returns>
        public XDocument GetProcedure(ScriptSchemaObjectBase ssob, Server svr, string version,string type)
        {
            return new XDocument(
                new XDeclaration("1.0","UTF-8","yes"),
                new XProcessingInstruction("xml-stylesheet", "type='text/xsl' href='style/procedure.xslt'"),
                new XElement("Procedure",new XAttribute("Type",type)
                    , new XAttribute("Name", ssob.Name)
                    , new XAttribute("Schema",ssob.Schema)
                    , new XElement("Properties"
                        , from Property p in ssob.Properties
                          select new XElement("Property"
                              , new XElement("Name", p.Name)
                              , new XElement("Value", p.Value)
                              ))
                    , new XElement("ExtendedProperties"
                        , from ExtendedProperty p in ((IExtendedProperties)ssob).ExtendedProperties
                          select new XElement("ExtendedProperty"
                              , new XElement("Name", p.Name)
                              , new XElement("Value", p.Value)
                              ))

                    , GetChildDependency(ssob, svr)
                    , GetParentDependency(ssob, svr)
                    , new XElement("Script", new XCData(GetScript(ssob, svr, version)))

                         ));
        }


        public XDocument GetDatabaseSummary(Database db)
        {
            XDocument xdoc = new XDocument(
                  new XDeclaration("1.0", "utf-8", "yes"),
                  new XProcessingInstruction("xml-stylesheet", "type='text/xsl' href='style/database.xslt'")
                  , new XElement("Database"
                      , new XAttribute("Id",db.Parent.Version.Major<9?"SQL Server 2000不支持该属性":db.DatabaseGuid.ToString())
                      , new XAttribute("Name", db.Name)
                      , new XAttribute("CreateTime", db.CreateDate.ToString())
                      , new XAttribute("Owner", db.Owner)
                      , new XElement("Properties"//生成基本属性有关信息
                          , new XAttribute("Count", db.Properties.Count)
                               , from Property p in db.Properties
                                 select new XElement("Property"
                                     , new XElement("Name", p.Name)
                                     , new XElement("Value", p.Value)
                                     )
                                  )
                      , new XElement("ExtendProperties"//生成扩展属性有关信息
                          , new XAttribute("Count", db.ExtendedProperties.Count)
                          , from ExtendedProperty p in db.ExtendedProperties
                            select new XElement("Property"
                                , new XElement("Name", p.Name)
                                , new XElement("Value", p.Value)
                                ))
                      , new XElement("FileGroups" //生成文件组有关信息
                          , new XAttribute("Count", db.FileGroups.Count)
                          , new XAttribute("Default", db.DefaultFileGroup)
                          , from FileGroup fg in db.FileGroups
                            select new XElement("FileGroup"
                                , new XElement("Name", fg.Name)
                                , new XElement("FilesCount", fg.Files.Count)
                                , new XElement("Files"
                                    , from DataFile f in fg.Files
                                      select new XElement("File"
                                          , new XElement("Name", f.Name)
                                          , new XElement("FileName", f.FileName)
                                          ))
                                , new XElement("IsDefault", fg.IsDefault)
                                , new XElement("IsReadOnly", fg.ReadOnly)
                                ))
                      , new XElement("LogFiles"
                          , new XAttribute("Count", db.LogFiles.Count)
                          , from LogFile l in db.LogFiles
                            select new XElement("LogFile"
                                , new XElement("Name", l.Name)
                                , new XElement("FileName", l.FileName)
                                ))

                      , new XElement("Users"
                          , new XAttribute("Count", db.Users.Count)
                          , from User u in db.Users
                            where !string.IsNullOrEmpty(u.Login)
                            select new XElement("User"
                                , new XElement("Name", u.Name)
                                , new XElement("LoginName", u.Login)
                                , new XElement("LoginType", u.LoginType)
                                ))
                      , new XElement("Tables"
                          , new XAttribute("Count", db.Tables.Count)
                          , from Table t in db.Tables
                            where !t.IsSystemObject
                            select new XElement("Table"
                                , new XElement("Schema", t.Schema)
                                , new XElement("Name", t.Name)
                                ))
                      , new XElement("Views"
                          , new XAttribute("Count", db.Views.Count)
                          , from Microsoft.SqlServer.Management.Smo.View v in db.Views
                            where !v.IsSystemObject
                            select new XElement("View"
                                , new XElement("Schema", v.Schema)
                                , new XElement("Name", v.Name)
                                ))
                      , new XElement("Functions"
                          , new XAttribute("Count", db.UserDefinedFunctions.Count)
                          , from UserDefinedFunction f in db.UserDefinedFunctions
                            where !f.IsSystemObject
                            select new XElement("Function"
                                , new XElement("Schema", f.Schema)
                                , new XElement("Name", f.Name)
                                ))

                      , new XElement("StoredProcedures"
                          , new XAttribute("Count", db.StoredProcedures.Count)
                          , from StoredProcedure p in db.StoredProcedures
                            where !p.IsSystemObject
                            select new XElement("StoredProcedure"
                                , new XElement("Schema", p.Schema)
                                , new XElement("Name", p.Name)
                                ))

                        ));

            return xdoc;
        }

        /// <summary>
        /// 这是取得数据库描述中的对象集合
        /// </summary>
        /// <param name="type">Tables,Views,UserFunctions,StoredProcedures</param>
        /// <param name="databasesummary">数据库描述的XML文档</param>
        /// <returns></returns>
        public XDocument GetObjectCollection(string type,XDocument databasesummary)
        {
            XElement temp = databasesummary.Root.Element(type);
            XDocument doc = new XDocument(
                new XDeclaration("1.0", "UTF-8", "yes"),
                new XProcessingInstruction("xml-stylesheet", "type='text/xsl' href='style/objectCollection.xslt'"),
                new XElement("Items",
                    new XAttribute("Type",type),
                    new XAttribute("Count", temp.Attribute("Count").Value),
                    from i in temp.Elements(type.Substring(0, type.Length - 1))
                    select new XElement("Item",
                        new XElement("Schema", i.Element("Schema").Value),
                        new XElement("Name", i.Element("Name").Value)
                        )));
            return doc;
                    
        }
    }
}
